/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cl.innovision.helper.utils;

import cl.innovision.vo.Cursor;
import cl.innovision.vo.InParameters;
import cl.innovision.vo.OutParameters;
import cl.innovision.vo.Parameter;
import cl.innovision.vo.Procedure;
import cl.innovision.vo.Procedures;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author PATO
 */
public class ConnectionDB {

    private static Connection conn = null;
    private static ConnectionDB singleton = null;
    
    private String db;
    private String host;
    private int puerto;
    private String instancia;
    private String esquema;
    private String usuario;
    private String contrasena;

    
    public static ConnectionDB getInstance(String db, String host, int puerto, String instancia, String esquema, String usuario, String contrasena) throws Exception {
        if (singleton == null) {
            singleton = new ConnectionDB( db,  host,  puerto,  instancia,  esquema,  usuario,  contrasena) ;
        }
        return singleton;
    }
    
    public static ConnectionDB getInstance() throws Exception {
        
        return singleton;
    }
    
    public ConnectionDB(String db, String host, int puerto, String instancia, String esquema, String usuario, String contrasena) throws Exception {
        this.db = db;
        this.host = host;
        this.puerto = puerto;
        this.instancia = instancia;
        this.esquema = esquema;
        this.usuario = usuario;
        this.contrasena = contrasena;
        getConnection();
        conn.setAutoCommit(false);
    }

    public Connection getConnection() throws Exception {

        if (db.equalsIgnoreCase("oracle")) {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
            } catch (ClassNotFoundException e) {
                throw new Exception("No pudo cargar driver jdbc " + "oracle.jdbc.driver.OracleDriver", e);

            }

            try {
                conn = DriverManager.getConnection("jdbc:oracle:thin:@" + this.host + ":" + this.puerto + ":" + this.instancia, this.usuario, this.contrasena);
            } catch (SQLException e) {
                throw new Exception("No pudo conectar a base de datos: " + e.getMessage());
            }
            return conn;
        } else if (db.equalsIgnoreCase("mysql")) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {

                throw new Exception("No pudo cargar driver jdbc " + "oracle.jdbc.driver.OracleDriver: " + e.getMessage());

            }
            try {
                conn = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.puerto + "/" + this.esquema, this.usuario, this.contrasena);
            } catch (SQLException e) {
                throw new Exception("No pudo conectar a base de datos: " + e.getMessage());
            }
            return conn;
        } else if (db.equalsIgnoreCase("sqlserver")) {
            try {
                Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            } catch (ClassNotFoundException e) {
                throw new Exception("No pudo cargar driver jdbc " + "oracle.jdbc.driver.OracleDriver", e);
            }
            try {
                conn = DriverManager.getConnection("jdbc:sqlserver:" + this.host + ":" + this.puerto + ":" + this.instancia, this.usuario, this.contrasena);
            } catch (SQLException e) {
                throw new Exception("No pudo conectar a base de datos ", e);
            }
            return conn;
        } else if (db.equalsIgnoreCase("postgresql")) {
            try {
                Class.forName("org.postgresql.Driver");
            } catch (ClassNotFoundException e) {
                throw new Exception("No pudo cargar driver jdbc " + "oracle.jdbc.driver.OracleDriver", e);
            }
            try {
                conn = DriverManager.getConnection("jdbc:postgresql:" + this.host + ":" + this.puerto + ":" + this.instancia, this.usuario, this.contrasena);
            } catch (SQLException e) {
                throw new Exception("No pudo conectar a base de datos ", e);
            }
            return conn;
        } else {
            return null;
        }
        

    }

    public ArrayList<String> getSchemas() throws Exception {
        ArrayList<String> v = new ArrayList<String>();
        DatabaseMetaData meta = conn.getMetaData();
        ResultSet schemas = meta.getSchemas();
        while (schemas.next()) {
            String tableSchema = schemas.getString(1);    // "TABLE_SCHEM"
            v.add(tableSchema);
            //String tableCatalog = schemas.getString(2); //"TABLE_CATALOG"
            System.out.println("tableSchema" + tableSchema);
        }
        return v;
    }
    
    private ArrayList<Cursor>  getResultSets(Procedure p) throws SQLException
        {
        System.out.println("------------------------------"+p.getName()+"------------------------------");    
        ArrayList<Cursor> cursores = new ArrayList<Cursor>();
       
            StringBuffer sb =new StringBuffer(p.getName()) ;
            sb.append("( ");
            for(int i=0;i<(p.getInParam().getParam().size()+p.getOutParam().getParam().size());i++)
            
                sb.append("?,");
            
            sb.deleteCharAt(sb.length()-1);
            sb.append(" )");
            System.out.println("sp: "+sb.toString());
            java.sql.CallableStatement cs = conn.prepareCall("{call  "+sb.toString()+" }");
            
            for(int i=0;i<p.getInParam().getParam().size();i++)
            {
                
                if(p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("number") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("int") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("money"))
                    cs.setInt(i+1, 0);
                else
                    cs.setString(i+1, null);
            }
             for(int o=0;o<p.getOutParam().getParam().size();o++)
            {
                
                if(p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("number") || p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("int") || p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("money"))
                    cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.NUMERIC);
                else
                     cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.VARCHAR);
                
            }
            boolean resultsAvailable=cs.execute();
            int ctCursor=0;
            while(resultsAvailable)
            {
                ResultSet rs=cs.getResultSet();
                ResultSetMetaData md = rs.getMetaData();
                Cursor c=new Cursor(p.getName()+"Cursor"+(ctCursor++));
                System.out.println("********Cursor: "+p.getName()+"Cursor"+(ctCursor++));
                for(int i=1;i<=md.getColumnCount();i++)
                {
                    String columnDataTypeName= Types.basicTypesDB(md.getColumnTypeName(i));
                    System.out.println("COLUMNA RS: "+md.getColumnName(i));
                    System.out.println("PRECISION: "+md.getPrecision(i));
                    System.out.println("ESCALA: "+md.getScale(i));
                    c.addRs(new Parameter(md.getColumnName(i),Types.db2class(columnDataTypeName,md.getPrecision(i),(short)md.getScale(i)),columnDataTypeName,md.getPrecision(i)));
                    
                }
                cursores.add(c);
                resultsAvailable = cs.getMoreResults();
            }
       
        return cursores;
    }
    
    
     public void getResultManual(Procedure p,String[] campos ) throws Exception
        {
        System.out.println("------------------------------"+p.getName()+"------------------------------");    
        ArrayList<Cursor> cursores = new ArrayList<Cursor>();
        
            StringBuffer sb =new StringBuffer(p.getName()) ;
            sb.append("( ");
            for(int i=0;i<(p.getInParam().getParam().size()+p.getOutParam().getParam().size());i++)
            
                sb.append("?,");
            
            sb.deleteCharAt(sb.length()-1);
            sb.append(" )");
            System.out.println("sp: "+sb.toString());
            java.sql.CallableStatement cs = conn.prepareCall("{call  "+sb.toString()+" }");
            
            for(int i=0;i<p.getInParam().getParam().size();i++)
            {
                
                if(p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("number") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("int") || p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("money"))
                    
                    cs.setInt(i+1, Integer.parseInt(campos[i]));
                else if (p.getInParam().getParam().get(i).getDbType().equalsIgnoreCase("DATE"))
                    cs.setDate(i+1, Formateador.string2SQLdate(campos[i]));
                 else
                    cs.setString(i+1, campos[i]);
            }
             for(int o=0;o<p.getOutParam().getParam().size();o++)
            {
                
                if(p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("number") || p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("int") || p.getInParam().getParam().get(o).getDbType().equalsIgnoreCase("money"))
                    cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.NUMERIC);
                else
                     cs.registerOutParameter(p.getInParam().getParam().size()+o+1, java.sql.Types.VARCHAR);
                
            }
            boolean resultsAvailable=cs.execute();
            int ctCursor=0;
            while(resultsAvailable)
            {
                ResultSet rs=cs.getResultSet();
                ResultSetMetaData md = rs.getMetaData();
                Cursor c=new Cursor(p.getName()+"Cursor"+(ctCursor++));
                System.out.println("********Cursor: "+p.getName()+"Cursor"+(ctCursor++));
                for(int i=1;i<=md.getColumnCount();i++)
                {
                    
                    String columnDataTypeName= Types.basicTypesDB(md.getColumnTypeName(i));
                    System.out.println("COLUMNA RS: "+md.getColumnName(i));
                    System.out.println("PRECISION: "+md.getPrecision(i));
                    System.out.println("ESCALA: "+md.getScale(i));
                    c.addRs(new Parameter(md.getColumnName(i),Types.db2class(columnDataTypeName,md.getPrecision(i),(short)md.getScale(i)),columnDataTypeName,md.getPrecision(i)));
                    
                }
                cursores.add(c);
                resultsAvailable = cs.getMoreResults();
            }
            
         p.setGenerable(true);
         p.getOutParam().setRs(cursores);
         Procedures.getInstance().updateProcedure(p);
         //conn.rollback();
    }
    

    public void getProcedures(String shemaPattern, String procedurePattern)  {
        try{
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rsProc = dbMetaData.getProcedures(conn.getCatalog(), shemaPattern, procedurePattern);
            Procedures.getInstance().removeAll();
            while (rsProc.next()) {
            try {
                String procedureCatalog = rsProc.getString(1);
                String procedureSchema = rsProc.getString(2);
                String procedureName = rsProc.getString(3);
                ResultSet rs = dbMetaData.getProcedureColumns(procedureCatalog, procedureSchema, procedureName, null);

                Procedure procedure = null;
                procedure = new Procedure(procedureCatalog, procedureSchema, procedureName);
                Procedures.getInstance().addPrecedures(procedure);
                InParameters inP=new InParameters();
                OutParameters outP=new OutParameters();
                while (rs.next()) {

                    String columnName = rs.getString(4).substring(0, rs.getString("COLUMN_NAME").length());
                    short columnType = rs.getShort("COLUMN_TYPE");
                    int columnDataType = rs.getInt("DATA_TYPE");
                    String columnDataTypeName = rs.getString("TYPE_NAME");
                    int columnPrecision = rs.getInt("PRECISION");
                    int columnByteLength = rs.getInt("LENGTH");
                    short columnScale = rs.getShort("SCALE");
                    short columnRadix = rs.getShort("RADIX");
                    short columnNullable = rs.getShort("NULLABLE");
                    String columnRemarks = rs.getString("REMARKS");
                    System.out.println("procedureName: " + procedureName);
                    System.out.println("columnName: " + columnName);
                    System.out.println("columnDataTypeName: " + columnDataTypeName);
                    System.out.println("columnPrecision: " + columnPrecision);
                    System.out.println("columnScale: " + columnScale);
                    
                    //log.debug("columnType: "+tipoColumna(columnType));
                    
                    columnDataTypeName= Types.basicTypesDB(columnDataTypeName);
                    if (columnType == DatabaseMetaData.procedureColumnIn) {
                        Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                         inP.addParam(p);
                    }
                     if (columnType == DatabaseMetaData.procedureColumnOut) {
                        Parameter p =new Parameter(columnName, Types.db2class(columnDataTypeName, columnPrecision, columnScale),columnDataTypeName,columnPrecision );
                        outP.addParam(p);
                    }
                   
                  
                   
                }
                
                procedure.setInParam(inP);
                procedure.setOutParam(outP);  
                try{
                outP.setRs(getResultSets(procedure));
                }
                catch (Exception e)
                {
                    procedure.setGenerable(false);
                    Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, e);
                }
                //procedure=dynamicExecute(conn,procedure);
            } catch (SQLException ex) {
                Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, ex);
            }
           
            }
        }
         catch (SQLException ex){
              Logger.getLogger(ConnectionDB.class.getName()).log(Level.SEVERE, null, ex);
        }
       
        
    }
    
    
    private String tipoColumna(int val)
        {
             switch (val) {
        case DatabaseMetaData.procedureColumnUnknown:
            return "PARAMETER_UNKNOWN";    
        case DatabaseMetaData.procedureColumnIn:
            return "PARAMETER_IN";
        case DatabaseMetaData.procedureColumnInOut:
            return "PARAMETER_INOUT";  
        case DatabaseMetaData.procedureColumnOut:
            return "PARAMETER_OUT";
        case DatabaseMetaData.procedureColumnReturn:
            return "RETURN";
        default:
            return "PARAMETER_UNKNOWN";    
        }
        }
}
